A Modern Database Management System (DBMS) provides views to separate a lower level database design from an application. Views are database objects having result sets defined by a query (the database views are not physically stored objects). Typically, the application can perform database query commands, such as operations of SELECT, UPDATE, INSERT and DELETE in SQL (Structured Query Language), against a view and does not need to be aware of the definition of the view and its underlying data sources.
One common definition of a view is a UNION ALL VIEW, which unifies data from multiple database (for example, multiple tables). For example, a UNION ALL view can represent a phone book of telephone numbers located in the Province of Ontario, which is a combination of tables representing phone books of Toronto and other areas of Ontario.
While it is known how operations such as SELECT, UPDATE and DELETE operate on such views, the INSERT operation poses problems. One such problem is that it is unclear into which base table to place a record that is being inserted through the UNION ALL view. There are four known approaches for addressing this problem that are known to the inventors.
A first approach avoids using the UNION ALL view for INSERT. Instead, an application provides logic that decides which record belongs into which table and then inserts the records accordingly. One drawback of this approach is that this solution violates the idea that the UNION ALL be encapsulated inside the view and the application does not need not to be aware of its makeup. Another drawback of this approach is that the procedural logic limits the performance of the solution.
A second approach extends the SQL INSERT statement to allow for inserts into multiple tables instead of just allowing insert into one table. This is accomplished by using predicates to decide which records belong in which table. This may address the performance problem, but the second approach appears to be poorly encapsulated.
A third approach uses INSTEAD OF triggers. These are descriptions (using the first and second approaches) which are executed whenever a specific operation (such as INSERT) is being performed against an UNION ALL view. This approach may solve the encapsulation problem. However, triggers tend to be heavy weight and thus do not perform well enough.
The three foregoing approaches may often cause excessive evaluation of the predicates used for dispatching the records into the respective target tables. The reason for this is that in these scenarios the target tables often have constraints that limit which records may be inserted into a given table, and these constraints are the same predicates used in the SQL dispatch statement so that in effect there is double the effort to evaluate the same predicate.) A predicate is a search condition specified in an SQL (Search Query Language) statement.)
In a fourth approach, a record that is inserted into an UNION ALL view is considered to belong to the table that will accept it as long as one, and only one table will accept the record. Otherwise the record shall be rejected. A known system provided by Microsoft™ in the product SQL Server 2000 may call for heavy restrictions on semantics in the fourth approach. Namely it requires that the constraints being defined on the target tables be very simple and it requires that the DBMS can prove that no record can exist which could belong to more than one table. These are very stringent rules which allow only for simple cases. For example, known database management systems (using the fourth approach) can handle INSERT entries into the above mentioned Ontario phone book if the base phone books are partitioned by the names of cities (a single column). But it cannot handle a design where a secondary column is also required, such as, for example if the Toronto phone book is split into two volumes my Names A–L and M–Z. Furthermore the known database management systems (using the fourth approach) are not capable of dealing with BEFORE TRIGGERs. Before triggers are modifications to the records made prior to inserting them into the target table. Since before triggers can perform different actions for different tables and they have to be executed before the decision is made into which table the modified record is placed this is not trivial.
Accordingly, a solution that addresses, at least in part, this and other shortcomings is desired.